package com.proxy.dao;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.proxy.util.Constants;
import com.proxy.util.DataSourceUtil;

/**
 * Proxy data access object with db
 *
 */
public class ProxyDao {
	
	/**
	 * Get data from proxies table with is_checked = b'0'
	 * @return
	 * @throws SQLException
	 */
	public List<Proxy> getProxyList() throws SQLException
	{
        try {
            QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
            List<Proxy> myProxyList = (List<Proxy>) qr.query( 
            		"select proxy from proxies where is_checked = b'0'", 
            		new BeanListHandler(Proxy.class));
            
            return myProxyList;
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
	}
	
	/**
	 * Update is_check=b'1' to proxies table from the proxy parameter object.
	 * @param myProxy
	 * @throws SQLException
	 */
	public void updateIsChecked(Proxy myProxy) throws SQLException
	{
        try {
            QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
            qr.update("UPDATE proxies SET is_checked = b'1' where proxy = ?", 
            				myProxy.getProxy());
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
	}
	
	/**
	 * Create proxy record or update record if record exits to specific table
	 * @param myProxy
	 * @param tableName
	 * @param updateTime
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	private void createOrUpdateSomeProxies(Proxy myProxy, String tableName, Date updateTime) throws SQLException
	{
        try {
//        	conn = DriverManager.getConnection(Constants.URL, Constants.USER, Constants.PASSWORD);
            QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
            Proxy tempProxy = (Proxy) qr.query( 
            		"SELECT * FROM " + tableName + " where proxy = ?", 
            			new BeanHandler(Proxy.class), 
            				myProxy.getProxy()); 
            if (tempProxy == null)
            {
            	String sql = "INSERT INTO " + tableName + " (proxy, last_update) VALUES (?,?)"; 
            	qr.update( sql, myProxy.getProxy(),updateTime); 
            }
            else
            {
            	String sql = "UPDATE " + tableName + " SET last_update=? WHERE proxy = ?"; 
            	qr.update( sql, updateTime,myProxy.getProxy()); 
            }
            
            
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } 
	}
	
	/**
	 * Create proxy record or update record if record exits to google_proxies table
	 * @param myProxy
	 * @throws SQLException
	 */
	public void createOrUpdateGoogleProxies(Proxy myProxy) throws SQLException
	{
        this.createOrUpdateSomeProxies(myProxy, Constants.GOOGLE_PROXIES_TABLE_NAME, myProxy.getUpdateTimeForGoogleHost());
	}
	
	/**
	 * Create proxy record or update record if record exits to other_proxies table
	 * @param myProxy
	 * @throws SQLException
	 */
	public void createOrUpdateOtherProxies(Proxy myProxy) throws SQLException
	{
        this.createOrUpdateSomeProxies(myProxy, Constants.WORKING_PROXIES_TABLE_NAME, myProxy.getUpdateTimeForOtherHost());
	}
	
	/**
	 * Create proxy record or update record if record exits to both_proxies table
	 * @param myProxy
	 * @throws SQLException
	 */
	public void createOrUpdateBothProxies(Proxy myProxy) throws SQLException
	{
        this.createOrUpdateSomeProxies(myProxy, Constants.BOTH_PROXIES_TABLE_NAME, new Date());
	}
	
	public static void main(String [] args) throws FileNotFoundException, SQLException
	{
		File file = new File("E:/Captcha.txt");
		Scanner scan=new Scanner(file);
		ProxyDao roxyDao = new ProxyDao();
		Proxy proxy = new Proxy();
		while(scan.hasNext())
		{
			String proxyStr = scan.nextLine();
			proxy.setProxy(proxyStr);
			roxyDao.createOrUpdateSomeProxies(proxy, "proxies", new Date());
		}
	}
}
